﻿using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using System.Configuration;

namespace Oledb
{
    public class OleMySql
    {
        public string ConnectionString
        {
            get { return ConfigurationManager.AppSettings["oledb_mysql_connectionString"]; }
        }

        private MySqlConnection Connection
        {
            get { return new MySqlConnection(ConnectionString); }
        }

        private MySqlCommand Command
        {
            get { return new MySqlCommand(); }
        }

        public int ExecuteNonQuery(string cmdTxt, MySqlParameter[] param)
        {
            using (MySqlConnection conn = Connection)
            {
                MySqlCommand cmd = Command;
                ParamFill(conn, cmd, param, cmdTxt);
                int result;
                try { result = cmd.ExecuteNonQuery(); }
                finally { Close(conn, cmd); }
                return result;
            }
        }

        public object ExecuteScalar(string cmdTxt, MySqlParameter[] param)
        {
            using (MySqlConnection conn = Connection)
            {
                MySqlCommand cmd = Command;
                ParamFill(conn, cmd, param, cmdTxt);
                object result = null;
                try { result = cmd.ExecuteScalar(); }
                finally { Close(conn, cmd); }
                return result;
            }
        }

        public MySqlDataReader ExecuteReader(string cmdTxt, MySqlParameter[] param)
        {
            using (MySqlConnection conn = Connection)
            {
                MySqlCommand cmd = Command;
                ParamFill(conn, cmd, param, cmdTxt);
                MySqlDataReader result = null;
                try { result = cmd.ExecuteReader(CommandBehavior.CloseConnection); }
                finally { Close(conn, cmd); }
                return result;
            }
        }

        public DataTable QueryTable(string cmdTxt, MySqlParameter[] param)
        {
            using (MySqlConnection conn = Connection)
            {
                MySqlCommand cmd = Command;
                var dapter = new MySqlDataAdapter(cmd);
                var result = new DataTable();
                ParamFill(conn, cmd, param, cmdTxt);
                dapter.Fill(result);
                return result;
            }
        }

        private void ParamFill(MySqlConnection conn, MySqlCommand cmd, IEnumerable<MySqlParameter> param, string cmdTxt)
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdTxt;
            if (param == null) return;

            foreach (var item in param) cmd.Parameters.Add(item);
        }

        private void Close(MySqlConnection conn, MySqlCommand cmd)
        {
            if (conn.State == ConnectionState.Open) conn.Close();
        }
    }
}